{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 第三阶段 - 第2讲：数据准备与Pandas基础\n",
    "\n",
    "## 学习目标\n",
    "- 掌握Pandas的核心数据结构（Series和DataFrame）\n",
    "- 熟练使用各种数据读取和导出方法\n",
    "- 掌握数据查看、选择、过滤的多种方式\n",
    "- 理解Pandas与Excel的操作对应关系\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 环境配置完成\n",
      "Pandas版本: 2.2.3\n"
     ]
    }
   ],
   "source": [
    "# 导入必要的库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from datetime import datetime, timedelta\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# 设置显示选项\n",
    "pd.set_option('display.max_columns', None)  # 显示所有列\n",
    "pd.set_option('display.max_rows', 100)      # 最多显示100行\n",
    "pd.set_option('display.width', None)        # 自动调整宽度\n",
    "pd.set_option('display.float_format', '{:.2f}'.format)  # 浮点数格式\n",
    "\n",
    "# 中文显示配置\n",
    "plt.rcParams['font.sans-serif'] = ['Arial Unicode MS', 'SimHei']\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n",
    "print(\"✅ 环境配置完成\")\n",
    "print(f\"Pandas版本: {pd.__version__}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、Pandas核心数据结构\n",
    "\n",
    "### 1.1 Series - 一维数据结构\n",
    "\n",
    "**定义**: Series是带标签的一维数组，可以存储任何数据类型\n",
    "\n",
    "**与Excel对比**: 类似Excel中的**一列数据**\n",
    "\n",
    "**结构**:\n",
    "```\n",
    "索引(Index)    值(Values)\n",
    "    0      →     100\n",
    "    1      →     200\n",
    "    2      →     300\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "从列表创建Series:\n",
      "0    1000\n",
      "1    1500\n",
      "2    1200\n",
      "3    1800\n",
      "4    2000\n",
      "dtype: int64\n",
      "数据类型: <class 'pandas.core.series.Series'>\n",
      "值的类型: int64\n"
     ]
    }
   ],
   "source": [
    "# 创建Series的多种方式\n",
    "\n",
    "# 方式1: 从列表创建\n",
    "sales = pd.Series([1000, 1500, 1200, 1800, 2000])\n",
    "print(\"从列表创建Series:\")\n",
    "print(sales)\n",
    "print(f\"数据类型: {type(sales)}\")\n",
    "print(f\"值的类型: {sales.dtype}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "带标签的Series:\n",
      "周一    1000\n",
      "周二    1500\n",
      "周三    1200\n",
      "周四    1800\n",
      "周五    2000\n",
      "Name: 销售额, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 方式2: 指定索引\n",
    "sales_named = pd.Series(\n",
    "    [1000, 1500, 1200, 1800, 2000],\n",
    "    index=['周一', '周二', '周三', '周四', '周五'],\n",
    "    name='销售额'\n",
    ")\n",
    "print(\"\\n带标签的Series:\")\n",
    "print(sales_named)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "从字典创建Series:\n",
      "iPhone 15       6999\n",
      "MacBook Pro    12999\n",
      "iPad Air        4999\n",
      "AirPods Pro     1999\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 方式3: 从字典创建\n",
    "product_prices = pd.Series({\n",
    "    'iPhone 15': 6999,\n",
    "    'MacBook Pro': 12999,\n",
    "    'iPad Air': 4999,\n",
    "    'AirPods Pro': 1999\n",
    "})\n",
    "print(\"\\n从字典创建Series:\")\n",
    "print(product_prices)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== Series基本操作 ===\n",
      "\n",
      "访问单个值 - 周一的销售额: ¥1,000\n",
      "访问多个值:\n",
      "周一    1000\n",
      "周三    1200\n",
      "周五    2000\n",
      "Name: 销售额, dtype: int64\n",
      "\n",
      "切片操作:\n",
      "周二    1500\n",
      "周三    1200\n",
      "周四    1800\n",
      "Name: 销售额, dtype: int64\n",
      "\n",
      "条件过滤(>1500):\n",
      "周四    1800\n",
      "周五    2000\n",
      "Name: 销售额, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# Series的基本操作\n",
    "print(\"=== Series基本操作 ===\")\n",
    "print(f\"\\n访问单个值 - 周一的销售额: ¥{sales_named['周一']:,}\")\n",
    "print(f\"访问多个值:\\n{sales_named[['周一', '周三', '周五']]}\")\n",
    "print(f\"\\n切片操作:\\n{sales_named['周二':'周四']}\")\n",
    "print(f\"\\n条件过滤(>1500):\\n{sales_named[sales_named > 1500]}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== Series统计方法 ===\n",
      "求和: ¥7,500\n",
      "均值: ¥1,500.00\n",
      "中位数: ¥1,500.00\n",
      "最大值: ¥2,000\n",
      "最小值: ¥1,000\n",
      "标准差: ¥412.31\n",
      "\n",
      "描述性统计:\n",
      "count      5.00\n",
      "mean    1500.00\n",
      "std      412.31\n",
      "min     1000.00\n",
      "25%     1200.00\n",
      "50%     1500.00\n",
      "75%     1800.00\n",
      "max     2000.00\n",
      "Name: 销售额, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Series的统计方法\n",
    "print(\"=== Series统计方法 ===\")\n",
    "print(f\"求和: ¥{sales_named.sum():,}\")\n",
    "print(f\"均值: ¥{sales_named.mean():,.2f}\")\n",
    "print(f\"中位数: ¥{sales_named.median():,.2f}\")\n",
    "print(f\"最大值: ¥{sales_named.max():,}\")\n",
    "print(f\"最小值: ¥{sales_named.min():,}\")\n",
    "print(f\"标准差: ¥{sales_named.std():,.2f}\")\n",
    "print(f\"\\n描述性统计:\\n{sales_named.describe()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2 DataFrame - 二维数据结构\n",
    "\n",
    "**定义**: DataFrame是带标签的二维表格数据结构，每列可以是不同的数据类型\n",
    "\n",
    "**与Excel对比**: 类似Excel中的**工作表(Worksheet)**\n",
    "\n",
    "**结构**:\n",
    "```\n",
    "        列名1    列名2    列名3\n",
    "索引0     A        1      True\n",
    "索引1     B        2      False\n",
    "索引2     C        3      True\n",
    "```\n",
    "\n",
    "**核心概念**:\n",
    "- **行(Rows)**: 每一条记录\n",
    "- **列(Columns)**: 每个字段/属性\n",
    "- **索引(Index)**: 行标签\n",
    "- **列名(Column Names)**: 列标签"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "从字典创建DataFrame:\n",
      "            产品    类别     单价   库存   是否促销\n",
      "0    iPhone 15    手机   6999   50   True\n",
      "1  MacBook Pro    电脑  12999   30  False\n",
      "2     iPad Air    平板   4999   80   True\n",
      "3  AirPods Pro    耳机   1999  120  False\n",
      "4  Apple Watch  智能手表   2999   60   True\n"
     ]
    }
   ],
   "source": [
    "# 创建DataFrame的多种方式\n",
    "\n",
    "# 方式1: 从字典创建（最常用）\n",
    "data = {\n",
    "    '产品': ['iPhone 15', 'MacBook Pro', 'iPad Air', 'AirPods Pro', 'Apple Watch'],\n",
    "    '类别': ['手机', '电脑', '平板', '耳机', '智能手表'],\n",
    "    '单价': [6999, 12999, 4999, 1999, 2999],\n",
    "    '库存': [50, 30, 80, 120, 60],\n",
    "    '是否促销': [True, False, True, False, True]\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "print(\"从字典创建DataFrame:\")\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "从列表创建DataFrame:\n",
      "   姓名  年龄  城市     月薪\n",
      "0  张三  28  北京   8000\n",
      "1  李四  35  上海  12000\n",
      "2  王五  42  广州  15000\n",
      "3  赵六  31  深圳  10000\n"
     ]
    }
   ],
   "source": [
    "# 方式2: 从列表的列表创建\n",
    "data_list = [\n",
    "    ['张三', 28, '北京', 8000],\n",
    "    ['李四', 35, '上海', 12000],\n",
    "    ['王五', 42, '广州', 15000],\n",
    "    ['赵六', 31, '深圳', 10000]\n",
    "]\n",
    "\n",
    "df_employees = pd.DataFrame(\n",
    "    data_list,\n",
    "    columns=['姓名', '年龄', '城市', '月薪']\n",
    ")\n",
    "print(\"\\n从列表创建DataFrame:\")\n",
    "print(df_employees)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "从字典列表创建DataFrame:\n",
      "  order_id      product  quantity  amount\n",
      "0     O001    iPhone 15         2   13998\n",
      "1     O002  MacBook Pro         1   12999\n",
      "2     O003     iPad Air         3   14997\n"
     ]
    }
   ],
   "source": [
    "# 方式3: 从字典的列表创建\n",
    "orders = [\n",
    "    {'order_id': 'O001', 'product': 'iPhone 15', 'quantity': 2, 'amount': 13998},\n",
    "    {'order_id': 'O002', 'product': 'MacBook Pro', 'quantity': 1, 'amount': 12999},\n",
    "    {'order_id': 'O003', 'product': 'iPad Air', 'quantity': 3, 'amount': 14997}\n",
    "]\n",
    "\n",
    "df_orders = pd.DataFrame(orders)\n",
    "print(\"\\n从字典列表创建DataFrame:\")\n",
    "print(df_orders)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== DataFrame基本属性 ===\n",
      "形状(行×列): (5, 5)\n",
      "总元素数: 25\n",
      "维度: 2\n",
      "\n",
      "列名:\n",
      "['产品', '类别', '单价', '库存', '是否促销']\n",
      "\n",
      "索引:\n",
      "[0, 1, 2, 3, 4]\n",
      "\n",
      "数据类型:\n",
      "产品      object\n",
      "类别      object\n",
      "单价       int64\n",
      "库存       int64\n",
      "是否促销      bool\n",
      "dtype: object\n"
     ]
    }
   ],
   "source": [
    "# DataFrame的基本属性\n",
    "print(\"=== DataFrame基本属性 ===\")\n",
    "print(f\"形状(行×列): {df.shape}\")\n",
    "print(f\"总元素数: {df.size}\")\n",
    "print(f\"维度: {df.ndim}\")\n",
    "print(f\"\\n列名:\\n{df.columns.tolist()}\")\n",
    "print(f\"\\n索引:\\n{df.index.tolist()}\")\n",
    "print(f\"\\n数据类型:\\n{df.dtypes}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 二、数据读取与导出\n",
    "\n",
    "### 2.1 读取CSV文件\n",
    "\n",
    "**Excel对比**: \n",
    "- Excel: 文件 → 打开 → 选择CSV文件\n",
    "- Pandas: `pd.read_csv()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 示例CSV文件已创建: sample_sales.csv\n"
     ]
    }
   ],
   "source": [
    "# 创建示例CSV数据\n",
    "sample_data = {\n",
    "    'date': ['2024-01-01', '2024-01-02', '2024-01-03', '2024-01-04', '2024-01-05'],\n",
    "    'product': ['iPhone', 'MacBook', 'iPad', 'iPhone', 'AirPods'],\n",
    "    'sales': [15, 8, 12, 18, 25],\n",
    "    'revenue': [104985, 103992, 59988, 125982, 49975]\n",
    "}\n",
    "df_sample = pd.DataFrame(sample_data)\n",
    "\n",
    "# 保存为CSV\n",
    "df_sample.to_csv('sample_sales.csv', index=False, encoding='utf-8')\n",
    "print(\"✅ 示例CSV文件已创建: sample_sales.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "读取的CSV数据:\n",
      "         date  product  sales  revenue\n",
      "0  2024-01-01   iPhone     15   104985\n",
      "1  2024-01-02  MacBook      8   103992\n",
      "2  2024-01-03     iPad     12    59988\n",
      "3  2024-01-04   iPhone     18   125982\n",
      "4  2024-01-05  AirPods     25    49975\n"
     ]
    }
   ],
   "source": [
    "# 读取CSV文件 - 基础用法\n",
    "df_read = pd.read_csv('sample_sales.csv')\n",
    "print(\"读取的CSV数据:\")\n",
    "print(df_read)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== read_csv()常用参数 ===\n",
      "\n",
      "高级读取结果:\n",
      "        date  product  sales  revenue\n",
      "0 2024-01-01   iPhone     15   104985\n",
      "1 2024-01-02  MacBook      8   103992\n",
      "2 2024-01-03     iPad     12    59988\n",
      "3 2024-01-04   iPhone     18   125982\n",
      "4 2024-01-05  AirPods     25    49975\n",
      "\n",
      "date列类型: datetime64[ns]\n"
     ]
    }
   ],
   "source": [
    "# 读取CSV - 高级参数\n",
    "print(\"=== read_csv()常用参数 ===\")\n",
    "\n",
    "# 参数示例\n",
    "df_advanced = pd.read_csv(\n",
    "    'sample_sales.csv',\n",
    "    encoding='utf-8',           # 编码格式\n",
    "    parse_dates=['date'],       # 自动解析日期列\n",
    "    dtype={'sales': 'int64'},   # 指定列类型\n",
    "    # nrows=3,                  # 只读取前3行\n",
    "    # skiprows=[1, 2],          # 跳过特定行\n",
    "    # usecols=['date', 'product', 'revenue']  # 只读取指定列\n",
    ")\n",
    "\n",
    "print(\"\\n高级读取结果:\")\n",
    "print(df_advanced)\n",
    "print(f\"\\ndate列类型: {df_advanced['date'].dtype}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 读取Excel文件\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 直接双击打开\n",
    "- Pandas: `pd.read_excel()`\n",
    "\n",
    "**优势**: Pandas可以读取多个工作表、指定范围、处理合并单元格等"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 示例Excel文件已创建: sample_sales.xlsx (包含2个工作表)\n"
     ]
    }
   ],
   "source": [
    "# 创建示例Excel文件\n",
    "with pd.ExcelWriter('sample_sales.xlsx', engine='openpyxl') as writer:\n",
    "    df_sample.to_excel(writer, sheet_name='销售数据', index=False)\n",
    "    df_employees.to_excel(writer, sheet_name='员工信息', index=False)\n",
    "\n",
    "print(\"✅ 示例Excel文件已创建: sample_sales.xlsx (包含2个工作表)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "读取Excel - 销售数据工作表:\n",
      "         date  product  sales  revenue\n",
      "0  2024-01-01   iPhone     15   104985\n",
      "1  2024-01-02  MacBook      8   103992\n",
      "2  2024-01-03     iPad     12    59988\n",
      "3  2024-01-04   iPhone     18   125982\n",
      "4  2024-01-05  AirPods     25    49975\n",
      "\n",
      "读取的工作表:\n",
      "['销售数据', '员工信息']\n",
      "\n",
      "员工信息:\n",
      "   姓名  年龄  城市     月薪\n",
      "0  张三  28  北京   8000\n",
      "1  李四  35  上海  12000\n",
      "2  王五  42  广州  15000\n",
      "3  赵六  31  深圳  10000\n"
     ]
    }
   ],
   "source": [
    "# 读取Excel文件\n",
    "\n",
    "# 读取指定工作表\n",
    "df_excel = pd.read_excel('sample_sales.xlsx', sheet_name='销售数据')\n",
    "print(\"读取Excel - 销售数据工作表:\")\n",
    "print(df_excel)\n",
    "\n",
    "# 读取多个工作表\n",
    "df_dict = pd.read_excel('sample_sales.xlsx', sheet_name=['销售数据', '员工信息'])\n",
    "print(\"\\n读取的工作表:\")\n",
    "print(list(df_dict.keys()))\n",
    "print(\"\\n员工信息:\")\n",
    "print(df_dict['员工信息'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== read_excel()常用参数 ===\n",
      "\n",
      "sheet_name: 工作表名称或索引，可以是列表读取多个\n",
      "header: 指定哪一行作为列名（默认0）\n",
      "usecols: 读取指定列 'A:C' 或 [0, 1, 2]\n",
      "skiprows: 跳过前N行\n",
      "nrows: 读取前N行\n",
      "dtype: 指定列类型\n",
      "parse_dates: 解析日期列\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Excel高级读取参数\n",
    "print(\"=== read_excel()常用参数 ===\")\n",
    "print(\"\"\"\n",
    "sheet_name: 工作表名称或索引，可以是列表读取多个\n",
    "header: 指定哪一行作为列名（默认0）\n",
    "usecols: 读取指定列 'A:C' 或 [0, 1, 2]\n",
    "skiprows: 跳过前N行\n",
    "nrows: 读取前N行\n",
    "dtype: 指定列类型\n",
    "parse_dates: 解析日期列\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 数据导出\n",
    "\n",
    "**支持格式**: CSV, Excel, JSON, SQL, HTML等"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✅ 已导出为CSV: products_export.csv\n",
      "✅ 已导出为Excel: products_export.xlsx\n",
      "✅ 已导出为JSON: products_export.json\n"
     ]
    }
   ],
   "source": [
    "# 导出为CSV\n",
    "df.to_csv(\n",
    "    'products_export.csv',\n",
    "    index=False,              # 不保存索引\n",
    "    encoding='utf-8-sig',     # UTF-8带BOM（Excel打开不乱码）\n",
    "    sep=','                   # 分隔符\n",
    ")\n",
    "print(\"✅ 已导出为CSV: products_export.csv\")\n",
    "\n",
    "# 导出为Excel\n",
    "df.to_excel(\n",
    "    'products_export.xlsx',\n",
    "    sheet_name='产品清单',\n",
    "    index=False,\n",
    "    engine='openpyxl'\n",
    ")\n",
    "print(\"✅ 已导出为Excel: products_export.xlsx\")\n",
    "\n",
    "# 导出为JSON\n",
    "df.to_json(\n",
    "    'products_export.json',\n",
    "    orient='records',         # 记录格式\n",
    "    force_ascii=False,        # 支持中文\n",
    "    indent=2                  # 美化格式\n",
    ")\n",
    "print(\"✅ 已导出为JSON: products_export.json\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 三、数据查看与探索\n",
    "\n",
    "### 3.1 快速查看\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 滚动浏览、Ctrl+End查看大小\n",
    "- Pandas: `head()`, `tail()`, `sample()`, `info()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "创建了包含100条记录的数据集\n"
     ]
    }
   ],
   "source": [
    "# 创建更大的示例数据集\n",
    "np.random.seed(42)\n",
    "n = 100\n",
    "\n",
    "large_data = {\n",
    "    'order_id': [f'ORD{str(i).zfill(5)}' for i in range(1, n+1)],\n",
    "    'date': pd.date_range('2024-01-01', periods=n, freq='D'),\n",
    "    'customer': np.random.choice(['张三', '李四', '王五', '赵六', '钱七'], n),\n",
    "    'product': np.random.choice(['iPhone', 'MacBook', 'iPad', 'AirPods', 'Watch'], n),\n",
    "    'quantity': np.random.randint(1, 10, n),\n",
    "    'unit_price': np.random.choice([999, 1999, 2999, 4999, 6999, 12999], n),\n",
    "    'region': np.random.choice(['华东', '华北', '华南', '华中', '西南'], n)\n",
    "}\n",
    "\n",
    "df_large = pd.DataFrame(large_data)\n",
    "df_large['total_amount'] = df_large['quantity'] * df_large['unit_price']\n",
    "\n",
    "print(f\"创建了包含{len(df_large)}条记录的数据集\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "前5行数据:\n",
      "   order_id       date customer  product  quantity  unit_price region  \\\n",
      "0  ORD00001 2024-01-01       赵六  AirPods         2        6999     西南   \n",
      "1  ORD00002 2024-01-02       钱七   iPhone         6         999     华东   \n",
      "2  ORD00003 2024-01-03       王五  AirPods         6       12999     华北   \n",
      "3  ORD00004 2024-01-04       钱七  MacBook         1        6999     华北   \n",
      "4  ORD00005 2024-01-05       钱七   iPhone         9        2999     华东   \n",
      "\n",
      "   total_amount  \n",
      "0         13998  \n",
      "1          5994  \n",
      "2         77994  \n",
      "3          6999  \n",
      "4         26991  \n",
      "\n",
      "前3行数据:\n",
      "   order_id       date customer  product  quantity  unit_price region  \\\n",
      "0  ORD00001 2024-01-01       赵六  AirPods         2        6999     西南   \n",
      "1  ORD00002 2024-01-02       钱七   iPhone         6         999     华东   \n",
      "2  ORD00003 2024-01-03       王五  AirPods         6       12999     华北   \n",
      "\n",
      "   total_amount  \n",
      "0         13998  \n",
      "1          5994  \n",
      "2         77994  \n"
     ]
    }
   ],
   "source": [
    "# 查看前几行\n",
    "print(\"前5行数据:\")\n",
    "print(df_large.head())\n",
    "\n",
    "print(\"\\n前3行数据:\")\n",
    "print(df_large.head(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "后5行数据:\n",
      "    order_id       date customer  product  quantity  unit_price region  \\\n",
      "95  ORD00096 2024-04-05       王五     iPad         5        1999     西南   \n",
      "96  ORD00097 2024-04-06       钱七     iPad         6        6999     华东   \n",
      "97  ORD00098 2024-04-07       李四   iPhone         3       12999     华中   \n",
      "98  ORD00099 2024-04-08       李四    Watch         9         999     华北   \n",
      "99  ORD00100 2024-04-09       张三  AirPods         5        4999     西南   \n",
      "\n",
      "    total_amount  \n",
      "95          9995  \n",
      "96         41994  \n",
      "97         38997  \n",
      "98          8991  \n",
      "99         24995  \n"
     ]
    }
   ],
   "source": [
    "# 查看最后几行\n",
    "print(\"后5行数据:\")\n",
    "print(df_large.tail())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "随机抽取5条记录:\n",
      "    order_id       date customer  product  quantity  unit_price region  \\\n",
      "79  ORD00080 2024-03-20       赵六   iPhone         6        4999     华北   \n",
      "99  ORD00100 2024-04-09       张三  AirPods         5        4999     西南   \n",
      "62  ORD00063 2024-03-03       钱七    Watch         3         999     华南   \n",
      "93  ORD00094 2024-04-03       王五  MacBook         1        1999     华东   \n",
      "20  ORD00021 2024-01-21       李四  MacBook         8       12999     华中   \n",
      "\n",
      "    total_amount  \n",
      "79         29994  \n",
      "99         24995  \n",
      "62          2997  \n",
      "93          1999  \n",
      "20        103992  \n"
     ]
    }
   ],
   "source": [
    "# 随机抽样查看\n",
    "print(\"随机抽取5条记录:\")\n",
    "print(df_large.sample(5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== 数据信息总览 ===\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 100 entries, 0 to 99\n",
      "Data columns (total 8 columns):\n",
      " #   Column        Non-Null Count  Dtype         \n",
      "---  ------        --------------  -----         \n",
      " 0   order_id      100 non-null    object        \n",
      " 1   date          100 non-null    datetime64[ns]\n",
      " 2   customer      100 non-null    object        \n",
      " 3   product       100 non-null    object        \n",
      " 4   quantity      100 non-null    int64         \n",
      " 5   unit_price    100 non-null    int64         \n",
      " 6   region        100 non-null    object        \n",
      " 7   total_amount  100 non-null    int64         \n",
      "dtypes: datetime64[ns](1), int64(3), object(4)\n",
      "memory usage: 6.4+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# 数据信息总览\n",
    "print(\"=== 数据信息总览 ===\")\n",
    "print(df_large.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.2 描述性统计\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 手动计算或使用数据分析工具\n",
    "- Pandas: `describe()` 一键生成"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数值列描述性统计:\n",
      "                      date  quantity  unit_price  total_amount\n",
      "count                  100    100.00      100.00        100.00\n",
      "mean   2024-02-19 12:00:00      4.69     5129.00      23895.31\n",
      "min    2024-01-01 00:00:00      1.00      999.00       1999.00\n",
      "25%    2024-01-25 18:00:00      3.00     1999.00       6999.00\n",
      "50%    2024-02-19 12:00:00      4.00     3999.00      13993.00\n",
      "75%    2024-03-15 06:00:00      7.00     6999.00      28495.50\n",
      "max    2024-04-09 00:00:00      9.00    12999.00     116991.00\n",
      "std                    NaN      2.64     4071.72      26328.88\n"
     ]
    }
   ],
   "source": [
    "# 数值列的描述性统计\n",
    "print(\"数值列描述性统计:\")\n",
    "print(df_large.describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "所有列描述性统计:\n",
      "        order_id                 date customer product  quantity  unit_price  \\\n",
      "count        100                  100      100     100    100.00      100.00   \n",
      "unique       100                  NaN        5       5       NaN         NaN   \n",
      "top     ORD00001                  NaN       赵六  iPhone       NaN         NaN   \n",
      "freq           1                  NaN       26      25       NaN         NaN   \n",
      "mean         NaN  2024-02-19 12:00:00      NaN     NaN      4.69     5129.00   \n",
      "min          NaN  2024-01-01 00:00:00      NaN     NaN      1.00      999.00   \n",
      "25%          NaN  2024-01-25 18:00:00      NaN     NaN      3.00     1999.00   \n",
      "50%          NaN  2024-02-19 12:00:00      NaN     NaN      4.00     3999.00   \n",
      "75%          NaN  2024-03-15 06:00:00      NaN     NaN      7.00     6999.00   \n",
      "max          NaN  2024-04-09 00:00:00      NaN     NaN      9.00    12999.00   \n",
      "std          NaN                  NaN      NaN     NaN      2.64     4071.72   \n",
      "\n",
      "       region  total_amount  \n",
      "count     100        100.00  \n",
      "unique      5           NaN  \n",
      "top        西南           NaN  \n",
      "freq       27           NaN  \n",
      "mean      NaN      23895.31  \n",
      "min       NaN       1999.00  \n",
      "25%       NaN       6999.00  \n",
      "50%       NaN      13993.00  \n",
      "75%       NaN      28495.50  \n",
      "max       NaN     116991.00  \n",
      "std       NaN      26328.88  \n"
     ]
    }
   ],
   "source": [
    "# 包含所有列的统计(包括非数值列)\n",
    "print(\"所有列描述性统计:\")\n",
    "print(df_large.describe(include='all'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== 分类列统计 ===\n",
      "\n",
      "产品分布:\n",
      "product\n",
      "iPhone     25\n",
      "AirPods    23\n",
      "iPad       21\n",
      "Watch      17\n",
      "MacBook    14\n",
      "Name: count, dtype: int64\n",
      "\n",
      "地区分布:\n",
      "region\n",
      "西南    27\n",
      "华北    25\n",
      "华中    18\n",
      "华东    17\n",
      "华南    13\n",
      "Name: count, dtype: int64\n",
      "\n",
      "客户分布:\n",
      "customer\n",
      "赵六    26\n",
      "李四    21\n",
      "钱七    19\n",
      "张三    18\n",
      "王五    16\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# 分类列统计\n",
    "print(\"=== 分类列统计 ===\")\n",
    "print(\"\\n产品分布:\")\n",
    "print(df_large['product'].value_counts())\n",
    "\n",
    "print(\"\\n地区分布:\")\n",
    "print(df_large['region'].value_counts())\n",
    "\n",
    "print(\"\\n客户分布:\")\n",
    "print(df_large['customer'].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 四、数据选择与过滤\n",
    "\n",
    "### 4.1 选择列\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 点击列头选中整列\n",
    "- Pandas: `df['列名']` 或 `df[['列1', '列2']]`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "选择单列 - 产品:\n",
      "0    AirPods\n",
      "1     iPhone\n",
      "2    AirPods\n",
      "3    MacBook\n",
      "4     iPhone\n",
      "Name: product, dtype: object\n",
      "类型: <class 'pandas.core.series.Series'>\n"
     ]
    }
   ],
   "source": [
    "# 选择单列（返回Series）\n",
    "print(\"选择单列 - 产品:\")\n",
    "print(df_large['product'].head())\n",
    "print(f\"类型: {type(df_large['product'])}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "选择多列:\n",
      "   order_id  product  quantity  total_amount\n",
      "0  ORD00001  AirPods         2         13998\n",
      "1  ORD00002   iPhone         6          5994\n",
      "2  ORD00003  AirPods         6         77994\n",
      "3  ORD00004  MacBook         1          6999\n",
      "4  ORD00005   iPhone         9         26991\n",
      "类型: <class 'pandas.core.frame.DataFrame'>\n"
     ]
    }
   ],
   "source": [
    "# 选择多列（返回DataFrame）\n",
    "print(\"选择多列:\")\n",
    "selected_cols = df_large[['order_id', 'product', 'quantity', 'total_amount']]\n",
    "print(selected_cols.head())\n",
    "print(f\"类型: {type(selected_cols)}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 选择行\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 点击行号选中整行\n",
    "- Pandas: `loc[]` (标签) 或 `iloc[]` (位置)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "iloc - 选择第1行（索引0）:\n",
      "order_id                   ORD00001\n",
      "date            2024-01-01 00:00:00\n",
      "customer                         赵六\n",
      "product                     AirPods\n",
      "quantity                          2\n",
      "unit_price                     6999\n",
      "region                           西南\n",
      "total_amount                  13998\n",
      "Name: 0, dtype: object\n",
      "\n",
      "iloc - 选择前3行:\n",
      "   order_id       date customer  product  quantity  unit_price region  \\\n",
      "0  ORD00001 2024-01-01       赵六  AirPods         2        6999     西南   \n",
      "1  ORD00002 2024-01-02       钱七   iPhone         6         999     华东   \n",
      "2  ORD00003 2024-01-03       王五  AirPods         6       12999     华北   \n",
      "\n",
      "   total_amount  \n",
      "0         13998  \n",
      "1          5994  \n",
      "2         77994  \n",
      "\n",
      "iloc - 选择第1、3、5行:\n",
      "   order_id       date customer  product  quantity  unit_price region  \\\n",
      "0  ORD00001 2024-01-01       赵六  AirPods         2        6999     西南   \n",
      "2  ORD00003 2024-01-03       王五  AirPods         6       12999     华北   \n",
      "4  ORD00005 2024-01-05       钱七   iPhone         9        2999     华东   \n",
      "\n",
      "   total_amount  \n",
      "0         13998  \n",
      "2         77994  \n",
      "4         26991  \n"
     ]
    }
   ],
   "source": [
    "# 使用iloc按位置选择（从0开始）\n",
    "print(\"iloc - 选择第1行（索引0）:\")\n",
    "print(df_large.iloc[0])\n",
    "\n",
    "print(\"\\niloc - 选择前3行:\")\n",
    "print(df_large.iloc[0:3])  # 0, 1, 2\n",
    "\n",
    "print(\"\\niloc - 选择第1、3、5行:\")\n",
    "print(df_large.iloc[[0, 2, 4]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "loc - 选择索引为0的行:\n",
      "order_id                   ORD00001\n",
      "date            2024-01-01 00:00:00\n",
      "customer                         赵六\n",
      "product                     AirPods\n",
      "quantity                          2\n",
      "unit_price                     6999\n",
      "region                           西南\n",
      "total_amount                  13998\n",
      "Name: 0, dtype: object\n",
      "\n",
      "loc - 选择索引0到2的行:\n",
      "   order_id       date customer  product  quantity  unit_price region  \\\n",
      "0  ORD00001 2024-01-01       赵六  AirPods         2        6999     西南   \n",
      "1  ORD00002 2024-01-02       钱七   iPhone         6         999     华东   \n",
      "2  ORD00003 2024-01-03       王五  AirPods         6       12999     华北   \n",
      "\n",
      "   total_amount  \n",
      "0         13998  \n",
      "1          5994  \n",
      "2         77994  \n"
     ]
    }
   ],
   "source": [
    "# 使用loc按标签选择\n",
    "print(\"loc - 选择索引为0的行:\")\n",
    "print(df_large.loc[0])\n",
    "\n",
    "print(\"\\nloc - 选择索引0到2的行:\")\n",
    "print(df_large.loc[0:2])  # 注意：包含结束索引！"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 同时选择行和列\n",
    "print(\"iloc - 前3行，前4列:\")\n",
    "print(df_large.iloc[0:3, 0:4])\n",
    "\n",
    "print(\"\\nloc - 前3行，指定列:\")\n",
    "print(df_large.loc[0:2, ['order_id', 'product', 'total_amount']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3 条件过滤\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 数据 → 筛选 → 勾选条件\n",
    "- Pandas: 布尔索引 `df[条件]`\n",
    "\n",
    "**核心思路**: 先创建布尔Series，再用它过滤DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "过滤：数量>5的订单\n",
      "满足条件的记录数: 40\n",
      "    order_id       date customer  product  quantity  unit_price region  \\\n",
      "1   ORD00002 2024-01-02       钱七   iPhone         6         999     华东   \n",
      "2   ORD00003 2024-01-03       王五  AirPods         6       12999     华北   \n",
      "4   ORD00005 2024-01-05       钱七   iPhone         9        2999     华东   \n",
      "5   ORD00006 2024-01-06       李四    Watch         6        4999     华北   \n",
      "13  ORD00014 2024-01-14       李四     iPad         7         999     华南   \n",
      "\n",
      "    total_amount  \n",
      "1           5994  \n",
      "2          77994  \n",
      "4          26991  \n",
      "5          29994  \n",
      "13          6993  \n"
     ]
    }
   ],
   "source": [
    "# 单条件过滤\n",
    "print(\"过滤：数量>5的订单\")\n",
    "filtered = df_large[df_large['quantity'] > 5]\n",
    "print(f\"满足条件的记录数: {len(filtered)}\")\n",
    "print(filtered.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "过滤：产品='iPhone' 且 数量>3\n",
      "满足条件的记录数: 17\n",
      "    order_id       date customer product  quantity  unit_price region  \\\n",
      "1   ORD00002 2024-01-02       钱七  iPhone         6         999     华东   \n",
      "4   ORD00005 2024-01-05       钱七  iPhone         9        2999     华东   \n",
      "14  ORD00015 2024-01-15       赵六  iPhone         4        1999     华中   \n",
      "18  ORD00019 2024-01-19       张三  iPhone         7       12999     华东   \n",
      "50  ORD00051 2024-02-20       赵六  iPhone         4        1999     华中   \n",
      "\n",
      "    total_amount  \n",
      "1           5994  \n",
      "4          26991  \n",
      "14          7996  \n",
      "18         90993  \n",
      "50          7996  \n"
     ]
    }
   ],
   "source": [
    "# 多条件过滤 - AND (&)\n",
    "print(\"过滤：产品='iPhone' 且 数量>3\")\n",
    "filtered_and = df_large[(df_large['product'] == 'iPhone') & (df_large['quantity'] > 3)]\n",
    "print(f\"满足条件的记录数: {len(filtered_and)}\")\n",
    "print(filtered_and.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "过滤：产品='iPhone' 或 产品='MacBook'\n",
      "满足条件的记录数: 39\n",
      "    order_id       date customer  product  quantity  unit_price region  \\\n",
      "1   ORD00002 2024-01-02       钱七   iPhone         6         999     华东   \n",
      "3   ORD00004 2024-01-04       钱七  MacBook         1        6999     华北   \n",
      "4   ORD00005 2024-01-05       钱七   iPhone         9        2999     华东   \n",
      "10  ORD00011 2024-01-11       赵六   iPhone         3         999     西南   \n",
      "14  ORD00015 2024-01-15       赵六   iPhone         4        1999     华中   \n",
      "\n",
      "    total_amount  \n",
      "1           5994  \n",
      "3           6999  \n",
      "4          26991  \n",
      "10          2997  \n",
      "14          7996  \n"
     ]
    }
   ],
   "source": [
    "# 多条件过滤 - OR (|)\n",
    "print(\"过滤：产品='iPhone' 或 产品='MacBook'\")\n",
    "filtered_or = df_large[(df_large['product'] == 'iPhone') | (df_large['product'] == 'MacBook')]\n",
    "print(f\"满足条件的记录数: {len(filtered_or)}\")\n",
    "print(filtered_or.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "过滤：地区为华东或华北\n",
      "满足条件的记录数: 42\n",
      "   order_id       date customer  product  quantity  unit_price region  \\\n",
      "1  ORD00002 2024-01-02       钱七   iPhone         6         999     华东   \n",
      "2  ORD00003 2024-01-03       王五  AirPods         6       12999     华北   \n",
      "3  ORD00004 2024-01-04       钱七  MacBook         1        6999     华北   \n",
      "4  ORD00005 2024-01-05       钱七   iPhone         9        2999     华东   \n",
      "5  ORD00006 2024-01-06       李四    Watch         6        4999     华北   \n",
      "\n",
      "   total_amount  \n",
      "1          5994  \n",
      "2         77994  \n",
      "3          6999  \n",
      "4         26991  \n",
      "5         29994  \n"
     ]
    }
   ],
   "source": [
    "# 使用isin()进行多值匹配\n",
    "print(\"过滤：地区为华东或华北\")\n",
    "filtered_isin = df_large[df_large['region'].isin(['华东', '华北'])]\n",
    "print(f\"满足条件的记录数: {len(filtered_isin)}\")\n",
    "print(filtered_isin.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "过滤：产品名包含'Mac'\n",
      "满足条件的记录数: 14\n",
      "    order_id       date customer  product  quantity  unit_price region  \\\n",
      "3   ORD00004 2024-01-04       钱七  MacBook         1        6999     华北   \n",
      "16  ORD00017 2024-01-17       赵六  MacBook         1        1999     华南   \n",
      "19  ORD00020 2024-01-20       赵六  MacBook         2        1999     西南   \n",
      "20  ORD00021 2024-01-21       李四  MacBook         8       12999     华中   \n",
      "37  ORD00038 2024-02-07       钱七  MacBook         9        6999     华中   \n",
      "\n",
      "    total_amount  \n",
      "3           6999  \n",
      "16          1999  \n",
      "19          3998  \n",
      "20        103992  \n",
      "37         62991  \n"
     ]
    }
   ],
   "source": [
    "# 字符串过滤\n",
    "print(\"过滤：产品名包含'Mac'\")\n",
    "filtered_str = df_large[df_large['product'].str.contains('Mac', na=False)]\n",
    "print(f\"满足条件的记录数: {len(filtered_str)}\")\n",
    "print(filtered_str.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 复杂条件过滤\n",
    "print(\"复杂过滤：总金额>20000 且 地区在['华东','华南'] 且 数量>=3\")\n",
    "complex_filter = df_large[\n",
    "    (df_large['total_amount'] > 20000) &\n",
    "    (df_large['region'].isin(['华东', '华南'])) &\n",
    "    (df_large['quantity'] >= 3)\n",
    "]\n",
    "print(f\"满足条件的记录数: {len(complex_filter)}\")\n",
    "print(complex_filter.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.4 query()方法\n",
    "\n",
    "**优势**: 更简洁的条件表达式，类似SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "使用query: 数量>5 且 单价>5000\n",
      "满足条件的记录数: 16\n",
      "    order_id       date customer  product  quantity  unit_price region  \\\n",
      "2   ORD00003 2024-01-03       王五  AirPods         6       12999     华北   \n",
      "15  ORD00016 2024-01-16       李四    Watch         9       12999     华北   \n",
      "18  ORD00019 2024-01-19       张三   iPhone         7       12999     华东   \n",
      "20  ORD00021 2024-01-21       李四  MacBook         8       12999     华中   \n",
      "37  ORD00038 2024-02-07       钱七  MacBook         9        6999     华中   \n",
      "\n",
      "    total_amount  \n",
      "2          77994  \n",
      "15        116991  \n",
      "18         90993  \n",
      "20        103992  \n",
      "37         62991  \n",
      "\n",
      "使用query: 产品为iPhone或MacBook\n",
      "满足条件的记录数: 39\n",
      "    order_id       date customer  product  quantity  unit_price region  \\\n",
      "1   ORD00002 2024-01-02       钱七   iPhone         6         999     华东   \n",
      "3   ORD00004 2024-01-04       钱七  MacBook         1        6999     华北   \n",
      "4   ORD00005 2024-01-05       钱七   iPhone         9        2999     华东   \n",
      "10  ORD00011 2024-01-11       赵六   iPhone         3         999     西南   \n",
      "14  ORD00015 2024-01-15       赵六   iPhone         4        1999     华中   \n",
      "\n",
      "    total_amount  \n",
      "1           5994  \n",
      "3           6999  \n",
      "4          26991  \n",
      "10          2997  \n",
      "14          7996  \n"
     ]
    }
   ],
   "source": [
    "# 使用query()进行过滤\n",
    "print(\"使用query: 数量>5 且 单价>5000\")\n",
    "result = df_large.query('quantity > 5 and unit_price > 5000')\n",
    "print(f\"满足条件的记录数: {len(result)}\")\n",
    "print(result.head())\n",
    "\n",
    "print(\"\\n使用query: 产品为iPhone或MacBook\")\n",
    "result2 = df_large.query('product in [\"iPhone\", \"MacBook\"]')\n",
    "print(f\"满足条件的记录数: {len(result2)}\")\n",
    "print(result2.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 五、排序操作\n",
    "\n",
    "### 5.1 按值排序\n",
    "\n",
    "**Excel对比**:\n",
    "- Excel: 数据 → 排序\n",
    "- Pandas: `sort_values()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "按总金额升序排序:\n",
      "    order_id       date customer  product  quantity  unit_price region  \\\n",
      "32  ORD00033 2024-02-02       赵六  AirPods         1        1999     华北   \n",
      "93  ORD00094 2024-04-03       王五  MacBook         1        1999     华东   \n",
      "21  ORD00022 2024-01-22       钱七  AirPods         1        1999     西南   \n",
      "16  ORD00017 2024-01-17       赵六  MacBook         1        1999     华南   \n",
      "26  ORD00027 2024-01-27       王五    Watch         3         999     华北   \n",
      "\n",
      "    total_amount  \n",
      "32          1999  \n",
      "93          1999  \n",
      "21          1999  \n",
      "16          1999  \n",
      "26          2997  \n"
     ]
    }
   ],
   "source": [
    "# 单列排序 - 升序\n",
    "print(\"按总金额升序排序:\")\n",
    "sorted_asc = df_large.sort_values('total_amount')\n",
    "print(sorted_asc.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "按总金额降序排序（TOP 5）:\n",
      "    order_id       date customer  product  quantity  unit_price region  \\\n",
      "15  ORD00016 2024-01-16       李四    Watch         9       12999     华北   \n",
      "52  ORD00053 2024-02-22       赵六   iPhone         8       12999     华北   \n",
      "20  ORD00021 2024-01-21       李四  MacBook         8       12999     华中   \n",
      "46  ORD00047 2024-02-16       李四  AirPods         8       12999     华北   \n",
      "18  ORD00019 2024-01-19       张三   iPhone         7       12999     华东   \n",
      "\n",
      "    total_amount  \n",
      "15        116991  \n",
      "52        103992  \n",
      "20        103992  \n",
      "46        103992  \n",
      "18         90993  \n"
     ]
    }
   ],
   "source": [
    "# 单列排序 - 降序\n",
    "print(\"按总金额降序排序（TOP 5）:\")\n",
    "sorted_desc = df_large.sort_values('total_amount', ascending=False)\n",
    "print(sorted_desc.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "先按地区升序，再按总金额降序:\n",
      "   region  product  total_amount\n",
      "18     华东   iPhone         90993\n",
      "42     华东  AirPods         55992\n",
      "63     华东   iPhone         44991\n",
      "96     华东     iPad         41994\n",
      "56     华东  AirPods         38997\n",
      "4      华东   iPhone         26991\n",
      "22     华东    Watch         26991\n",
      "9      华东     iPad         20997\n",
      "47     华东     iPad         19996\n",
      "55     华东   iPhone         17991\n"
     ]
    }
   ],
   "source": [
    "# 多列排序\n",
    "print(\"先按地区升序，再按总金额降序:\")\n",
    "sorted_multi = df_large.sort_values(\n",
    "    ['region', 'total_amount'],\n",
    "    ascending=[True, False]\n",
    ")\n",
    "print(sorted_multi[['region', 'product', 'total_amount']].head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5.2 按索引排序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "打乱后的前5行索引:\n",
      "[3, 99, 77, 65, 81]\n",
      "\n",
      "按索引排序后的前5行索引:\n",
      "[0, 1, 2, 3, 4]\n"
     ]
    }
   ],
   "source": [
    "# 按索引排序\n",
    "shuffled = df_large.sample(frac=1)  # 随机打乱\n",
    "print(\"打乱后的前5行索引:\")\n",
    "print(shuffled.index[:5].tolist())\n",
    "\n",
    "sorted_by_index = shuffled.sort_index()\n",
    "print(\"\\n按索引排序后的前5行索引:\")\n",
    "print(sorted_by_index.index[:5].tolist())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 六、Pandas vs Excel 对照表\n",
    "\n",
    "### 常用操作对比\n",
    "\n",
    "| 操作 | Excel | Pandas |\n",
    "|------|-------|--------|\n",
    "| 查看前几行 | 滚动到顶部 | `df.head()` |\n",
    "| 查看数据信息 | 手动查看 | `df.info()`, `df.describe()` |\n",
    "| 选择列 | 点击列头 | `df['列名']` 或 `df[['列1','列2']]` |\n",
    "| 选择行 | 点击行号 | `df.iloc[0:5]` 或 `df.loc[0:4]` |\n",
    "| 筛选数据 | 数据→筛选 | `df[df['列'] > 值]` |\n",
    "| 排序 | 数据→排序 | `df.sort_values('列')` |\n",
    "| 去重 | 数据→删除重复项 | `df.drop_duplicates()` |\n",
    "| 求和 | `=SUM()` | `df['列'].sum()` |\n",
    "| 均值 | `=AVERAGE()` | `df['列'].mean()` |\n",
    "| 计数 | `=COUNT()` | `df['列'].count()` |\n",
    "| 最大值 | `=MAX()` | `df['列'].max()` |\n",
    "| 分组汇总 | 数据透视表 | `df.groupby()` |\n",
    "| 条件计数 | `=COUNTIF()` | `(df['列']>值).sum()` |\n",
    "| 添加列 | 在列末输入公式 | `df['新列'] = 表达式` |\n",
    "| 删除列 | 右键→删除 | `df.drop('列', axis=1)` |\n",
    "| 保存文件 | Ctrl+S | `df.to_csv()`, `df.to_excel()` |\n",
    "\n",
    "### Pandas的优势\n",
    "\n",
    "1. **处理大数据**: 百万行数据轻松处理\n",
    "2. **自动化**: 一次编写，重复使用\n",
    "3. **可重现**: 代码记录所有步骤\n",
    "4. **灵活性**: 复杂条件、多表关联\n",
    "5. **效率**: 批量处理多个文件\n",
    "\n",
    "### Excel的优势\n",
    "\n",
    "1. **可视化**: 直观的表格界面\n",
    "2. **易上手**: 无需编程基础\n",
    "3. **即时反馈**: 实时看到修改结果\n",
    "4. **格式丰富**: 单元格格式、条件格式\n",
    "\n",
    "### 建议\n",
    "\n",
    "- **小数据 + 一次性分析** → Excel\n",
    "- **大数据 + 重复性工作** → Pandas\n",
    "- **最佳实践**: Pandas处理 + Excel展示"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 七、综合练习\n",
    "\n",
    "### 练习1: 销售数据分析\n",
    "使用`df_large`数据集，完成以下任务:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== 任务1: TOP 10订单 ===\n",
      "    order_id  product  quantity  total_amount\n",
      "15  ORD00016    Watch         9        116991\n",
      "20  ORD00021  MacBook         8        103992\n",
      "46  ORD00047  AirPods         8        103992\n",
      "52  ORD00053   iPhone         8        103992\n",
      "18  ORD00019   iPhone         7         90993\n",
      "2   ORD00003  AirPods         6         77994\n",
      "61  ORD00062   iPhone         6         77994\n",
      "77  ORD00078   iPhone         6         77994\n",
      "80  ORD00081   iPhone         6         77994\n",
      "37  ORD00038  MacBook         9         62991\n"
     ]
    }
   ],
   "source": [
    "# 任务1: 找出总金额TOP 10的订单\n",
    "print(\"=== 任务1: TOP 10订单 ===\")\n",
    "top10 = df_large.nlargest(10, 'total_amount')\n",
    "print(top10[['order_id', 'product', 'quantity', 'total_amount']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "=== 任务2: 条件筛选 ===\n",
      "满足条件的订单数: 5\n",
      "    order_id       date customer product  quantity  unit_price region  \\\n",
      "1   ORD00002 2024-01-02       钱七  iPhone         6         999     华东   \n",
      "4   ORD00005 2024-01-05       钱七  iPhone         9        2999     华东   \n",
      "18  ORD00019 2024-01-19       张三  iPhone         7       12999     华东   \n",
      "55  ORD00056 2024-02-25       张三  iPhone         9        1999     华东   \n",
      "63  ORD00064 2024-03-04       李四  iPhone         9        4999     华东   \n",
      "\n",
      "    total_amount  \n",
      "1           5994  \n",
      "4          26991  \n",
      "18         90993  \n",
      "55         17991  \n",
      "63         44991  \n"
     ]
    }
   ],
   "source": [
    "# 任务2: 筛选出华东地区、产品为iPhone、数量>=3的订单\n",
    "print(\"\\n=== 任务2: 条件筛选 ===\")\n",
    "filtered = df_large[\n",
    "    (df_large['region'] == '华东') &\n",
    "    (df_large['product'] == 'iPhone') &\n",
    "    (df_large['quantity'] >= 3)\n",
    "]\n",
    "print(f\"满足条件的订单数: {len(filtered)}\")\n",
    "print(filtered.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "=== 任务3: 产品销售统计 ===\n",
      "         总销量     总金额  订单数\n",
      "product                  \n",
      "iPhone   129  789871   25\n",
      "AirPods  102  583898   23\n",
      "Watch     95  409905   17\n",
      "MacBook   52  321948   14\n",
      "iPad      91  283909   21\n"
     ]
    }
   ],
   "source": [
    "# 任务3: 统计每个产品的销售数量和总金额\n",
    "print(\"\\n=== 任务3: 产品销售统计 ===\")\n",
    "product_summary = df_large.groupby('product').agg({\n",
    "    'quantity': 'sum',\n",
    "    'total_amount': 'sum',\n",
    "    'order_id': 'count'\n",
    "})\n",
    "product_summary.columns = ['总销量', '总金额', '订单数']\n",
    "product_summary = product_summary.sort_values('总金额', ascending=False)\n",
    "print(product_summary)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "=== 任务4: TOP 5客户 ===\n",
      "             总消费  订单数\n",
      "customer             \n",
      "赵六        686885   26\n",
      "李四        664890   21\n",
      "钱七        479904   19\n",
      "张三        330917   18\n",
      "王五        226935   16\n"
     ]
    }
   ],
   "source": [
    "# 任务4: 找出购买最多的客户TOP 5\n",
    "print(\"\\n=== 任务4: TOP 5客户 ===\")\n",
    "customer_summary = df_large.groupby('customer').agg({\n",
    "    'total_amount': 'sum',\n",
    "    'order_id': 'count'\n",
    "}).sort_values('total_amount', ascending=False)\n",
    "customer_summary.columns = ['总消费', '订单数']\n",
    "print(customer_summary.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 练习2: 数据处理流程\n",
    "完成一个完整的数据处理流程"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== 完整数据处理流程 ===\n",
      "1. 原始数据: 100行\n",
      "2. 筛选后: 58行\n",
      "3. 产品筛选后: 24行\n",
      "\n",
      "✅ 处理完成，已导出到 high_value_orders.xlsx\n",
      "\n",
      "最终结果预览:\n",
      "    order_id       date  product  quantity  total_amount region\n",
      "20  ORD00021 2024-01-21  MacBook         8        103992     华中\n",
      "52  ORD00053 2024-02-22   iPhone         8        103992     华北\n",
      "18  ORD00019 2024-01-19   iPhone         7         90993     华东\n",
      "80  ORD00081 2024-03-21   iPhone         6         77994     华北\n",
      "77  ORD00078 2024-03-18   iPhone         6         77994     华北\n",
      "61  ORD00062 2024-03-02   iPhone         6         77994     华中\n",
      "37  ORD00038 2024-02-07  MacBook         9         62991     华中\n",
      "71  ORD00072 2024-03-12   iPhone         8         55992     华北\n",
      "63  ORD00064 2024-03-04   iPhone         9         44991     华东\n",
      "83  ORD00084 2024-03-24  MacBook         6         41994     华南\n"
     ]
    }
   ],
   "source": [
    "# 完整流程示例\n",
    "print(\"=== 完整数据处理流程 ===\")\n",
    "\n",
    "# 1. 读取数据\n",
    "df_process = df_large.copy()\n",
    "print(f\"1. 原始数据: {len(df_process)}行\")\n",
    "\n",
    "# 2. 筛选高价值订单（>10000）\n",
    "df_process = df_process[df_process['total_amount'] > 10000]\n",
    "print(f\"2. 筛选后: {len(df_process)}行\")\n",
    "\n",
    "# 3. 只保留特定产品\n",
    "df_process = df_process[df_process['product'].isin(['iPhone', 'MacBook'])]\n",
    "print(f\"3. 产品筛选后: {len(df_process)}行\")\n",
    "\n",
    "# 4. 按总金额降序排序\n",
    "df_process = df_process.sort_values('total_amount', ascending=False)\n",
    "\n",
    "# 5. 选择需要的列\n",
    "df_final = df_process[['order_id', 'date', 'product', 'quantity', 'total_amount', 'region']]\n",
    "\n",
    "# 6. 导出结果\n",
    "df_final.to_excel('high_value_orders.xlsx', index=False)\n",
    "print(\"\\n✅ 处理完成，已导出到 high_value_orders.xlsx\")\n",
    "print(\"\\n最终结果预览:\")\n",
    "print(df_final.head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## 八、本讲总结\n",
    "\n",
    "### 核心知识点\n",
    "\n",
    "1. **数据结构**\n",
    "   - Series: 一维数组（类似Excel的一列）\n",
    "   - DataFrame: 二维表格（类似Excel工作表）\n",
    "\n",
    "2. **数据读取**\n",
    "   - CSV: `pd.read_csv()`\n",
    "   - Excel: `pd.read_excel()`\n",
    "   - 导出: `to_csv()`, `to_excel()`, `to_json()`\n",
    "\n",
    "3. **数据查看**\n",
    "   - 快速查看: `head()`, `tail()`, `sample()`\n",
    "   - 信息总览: `info()`, `describe()`\n",
    "   - 频数统计: `value_counts()`\n",
    "\n",
    "4. **数据选择**\n",
    "   - 选列: `df['列']`, `df[['列1','列2']]`\n",
    "   - 选行: `iloc[]` (位置), `loc[]` (标签)\n",
    "   - 过滤: 布尔索引, `query()`, `isin()`\n",
    "\n",
    "5. **数据排序**\n",
    "   - 按值: `sort_values()`\n",
    "   - 按索引: `sort_index()`\n",
    "   - 多列排序: 传入列表\n",
    "\n",
    "### 关键对比\n",
    "\n",
    "| 概念 | Pandas | Excel |\n",
    "|------|--------|-------|\n",
    "| 数据结构 | DataFrame | 工作表 |\n",
    "| 列 | Series | 列 |\n",
    "| 索引 | Index | 行号 |\n",
    "| 筛选 | 布尔索引 | 筛选器 |\n",
    "| 排序 | sort_values() | 排序 |\n",
    "| 函数 | sum(), mean() | SUM(), AVERAGE() |\n",
    "\n",
    "### 下节预告\n",
    "**第3讲: 缺失值与异常值处理**\n",
    "- 缺失值的检测与处理策略\n",
    "- 异常值的识别方法（IQR、3σ）\n",
    "- 实战案例：清洗真实脏数据\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 课后作业\n",
    "\n",
    "### 作业1: 基础操作练习\n",
    "使用生成的`df_large`数据集，完成:\n",
    "1. 筛选出\"MacBook\"产品且总金额>30000的订单\n",
    "2. 按地区分组，计算每个地区的平均订单金额\n",
    "3. 找出每个产品的最大单笔订单金额\n",
    "4. 统计每个客户的购买次数和总消费\n",
    "5. 导出结果到Excel，包含多个工作表\n",
    "\n",
    "### 作业2: Excel对比练习\n",
    "用Excel和Pandas分别完成同一个任务，记录时间和步骤差异:\n",
    "- 任务：筛选销售额>15000的记录，按地区分组求和，降序排列\n",
    "- 对比：操作步骤数、耗时、可重复性\n",
    "\n",
    "### 作业3: 综合应用\n",
    "创建一个自动化脚本:\n",
    "1. 读取3个不同的CSV文件\n",
    "2. 对每个文件进行数据清洗和筛选\n",
    "3. 合并结果\n",
    "4. 生成统计报告\n",
    "5. 导出为格式化的Excel文件"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:base] *",
   "language": "python",
   "name": "conda-base-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
